import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

/**
 * Created by Administrator on 10/23/2016.
 */

//main函数和POI都在这个类中
public class Main {
    public static void main(String[] args) throws IOException
    {
        Picture.cutPicture();
        Picture.shrinkPicture();
        Picture.enlargedPicture();


        String filePath="data\\sample.xls";
        HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件,名称是workbook
        HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表,名称是sheet
        HSSFRow row=sheet.createRow(0); //创建第0行
        row.createCell(0).setCellValue("Tom");  //创建第0行第0个单元格
        row.createCell(1).setCellValue("California");   //创建第0行第1个单元格
        row.createCell(2).setCellValue("138127");   //创建第0行第2个单元格

        //1.Read Cells
        System.out.println(sheet.getRow(0).getCell(0)+"\n"+
                           sheet.getRow(0).getCell(1)+"\n"+
                           sheet.getRow(0).getCell(2));

        //2.set the format of a cell
        //2.1设置预定义的日期格式
        HSSFRow row1=sheet.createRow(1);
        HSSFCell cell=row1.createCell(0);
        cell.setCellValue(new Date());
        HSSFCellStyle styleDate = workbook.createCellStyle();  //创建单元格格式变量,名为styleDate
        styleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("d-mmm-yy"));  //需使用符合要求的格式
        cell.setCellStyle(styleDate);  //给cell这个单元格设置格式，该函数形参为一个单元格格式变量
        System.out.println(cell);

        //2.2设置温度自定义格式
        HSSFCell cell2=row1.createCell(2);
        cell2.setCellValue(38.6);
        HSSFCellStyle styleTemperature = workbook.createCellStyle();
        //用setDataFormat()函数给单元格格式名称变量赋值，createDataFormat()代表自定义格式
        //excel的自定义格式以#符号代表数字出现的位置
        styleTemperature.setDataFormat(workbook.createDataFormat().getFormat("##.#℃"));
        cell2.setCellStyle(styleTemperature);
        System.out.println(cell2);

        //2.3设置科学计数法自定义格式
        HSSFCell cell1=row1.createCell(1);
        cell1.setCellValue(233333333);
        HSSFCellStyle styleSCount = workbook.createCellStyle();
        styleSCount.setDataFormat(workbook.createDataFormat().getFormat("0.00E+00"));
        cell1.setCellStyle(styleSCount);
        System.out.println(cell1);

        //3.Merge Cells
        HSSFRow row2=sheet.createRow(2);
        HSSFCell cell_2=row2.createCell(0);
        cell_2.setCellValue("合并之后的单元格");
        //CellRangeAddress(x1, x2, y1, y2)用于构造合并的单元格
        CellRangeAddress region=new CellRangeAddress(2, 3, 0, 3);
        sheet.addMergedRegion(region);

        //4.Change alignment
        HSSFRow row4=sheet.createRow(4);
        HSSFCell cell_3=row4.createCell(0);
        sheet.setColumnWidth(0,30*256); //将第0列的宽度设置长一点
        row4.setHeightInPoints(50);
        cell_3.setCellValue("单元格对齐单元格对齐");
        HSSFCellStyle style=workbook.createCellStyle();   //对齐格式以style的格式对单元格进行设置
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);  //水平方向上向右对齐
        style.setWrapText(true);   //设置自动换行
        cell_3.setCellStyle(style);

        HSSFCell cell_4=row4.createCell(1);
        sheet.setColumnWidth(1,30*256); //将第1列的宽度设置长一点
        cell_4.setCellValue("单元格对齐单元格对齐");
        HSSFCellStyle style2=workbook.createCellStyle();   //对齐格式以style的格式对单元格进行设置
        style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);  //水平方向上向右对齐
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);  //垂直方向上向下对齐
        style2.setWrapText(true);   //设置自动换行
        style2.setRotation((short)-90);
        cell_4.setCellStyle(style2);

        //5.Set a formula for a cell
        //Lookup和VLookkup函数
        HSSFSheet sheet2=workbook.createSheet("Test2"); //创建了一个新表Test2
        HSSFRow row_1=sheet2.createRow(0);
        row_1.createCell(0).setCellValue("点数");
        row_1.createCell(1).setCellValue("级别");
        row_1=sheet2.createRow(1);
        row_1.createCell(0).setCellValue(1);
        row_1.createCell(1).setCellValue("小");
        row_1=sheet2.createRow(2);
        row_1.createCell(0).setCellValue(2);
        row_1.createCell(1).setCellValue("小");
        row_1=sheet2.createRow(3);
        row_1.createCell(0).setCellValue(3);
        row_1.createCell(1).setCellValue("中");
        row_1=sheet2.createRow(4);
        row_1.createCell(0).setCellValue(4);
        row_1.createCell(1).setCellValue("中");
        row_1=sheet2.createRow(5);
        row_1.createCell(0).setCellValue(5);
        row_1.createCell(1).setCellValue("大");
        row_1=sheet2.createRow(6);
        row_1.createCell(0).setCellValue(6);
        row_1.createCell(1).setCellValue("大");
        row_1=sheet2.createRow(7);
        row_1.createCell(0).setCellValue(4);    //查找：4属于哪个等级
        row_1.createCell(1).setCellFormula("VLOOKUP(A8,$A$2:$B$7,2,false)");
        HSSFFont font=workbook.createFont();
        font.setColor(HSSFColor.RED.index);
        HSSFCellStyle style3=workbook.createCellStyle();
        style3.setFont(font);
        row_1.getCell(1).setCellStyle(style3);  //给查找结果设置字体为红色

        FileOutputStream out = new FileOutputStream(filePath);  //创建文件输出流
        workbook.write(out);//保存Excel文件
        out.close();//关闭文件流
        System.out.println("OK!");

    }
}
